package com.dd.stack.sql.optimize.util;

import java.sql.*;
import java.time.LocalDate;
import java.util.HashSet;
import java.util.Random;

/**
 * @Author liuxianmeng
 * @CreateTime 2025/8/12 17:10
 * @Description 类功能作用说明
 */
public class DataGenerator {

    //private static final String URL = "jdbc:mysql://localhost:3306/dd_medical?rewriteBatchedStatements=true";
    private static final String URL = "jdbc:mysql://localhost:3306/dd_medical_no_index?rewriteBatchedStatements=true";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    // 样本数据
    private static final String[] DEPT_NAMES = {"Cardiology", "Neurology", "Oncology", "Pediatrics", "Orthopedics"};
    private static final String[] LOCATIONS = {"East Wing", "West Wing", "North Wing", "South Wing"};
    private static final String[] TITLES = {"Resident", "Specialist", "Consultant", "Director"};
    private static final String[] MEDICINES = {
            "Aspirin", "Lisinopril", "Metformin", "Atorvastatin", "Levothyroxine",
            "Amlodipine", "Omeprazole", "Albuterol", "Insulin", "Gabapentin"
    };
    private static final String[] DOSAGES = {"1*QD", "1*BID", "1*TID", "2*QD", "1*QOD"};
    private static final String[] DIAGNOSES = {
            "Hypertension", "Type 2 Diabetes", "Coronary Artery Disease",
            "Asthma", "Osteoarthritis", "Migraine", "Hyperlipidemia"
    };

    /**
     * 逐个表插入数据 避免不可控因素易导致出现插入失败
     */
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            conn.setAutoCommit(false);

            // 1. 插入科室数据
            //insertDepartments(conn, 5);

            // 2. 插入医生数据
            //insertDoctors(conn, 50);

            // 3. 插入患者数据
            //insertPatients(conn, 10000);

            // 4. 插入药品数据
            //insertMedicines(conn, 10);

            // 5. 插入病历数据
            //insertMedicalRecords(conn, 50000);

            // 6. 插入处方数据
            //insertPrescriptions(conn, 150000);

            // 7. 更新病人名字为随机字符串
            updatePatientNames(conn, 10001, 20000);

            conn.commit();
            System.out.println("执行完成!");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void insertDepartments(Connection conn, int count) throws SQLException {
        String sql = "INSERT INTO department (dept_name, dept_location) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (int i = 0; i < count; i++) {
                pstmt.setString(1, DEPT_NAMES[i]);
                pstmt.setString(2, LOCATIONS[i % LOCATIONS.length]);
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        }
    }

    private static void insertDoctors(Connection conn, int count) throws SQLException {
        String sql = "INSERT INTO doctor (doc_name, dept_id, doc_title, doc_salary, doc_hire_date) VALUES (?, ?, ?, ?, ?)";
        Random random = new Random();

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (int i = 1; i <= count; i++) {
                pstmt.setString(1, "Doctor_" + i);
                pstmt.setInt(2, random.nextInt(5) + 1);  // dept_id 1-5
                pstmt.setString(3, TITLES[random.nextInt(TITLES.length)]);
                pstmt.setDouble(4, 8000 + random.nextInt(15000)); // salary 8000-23000
                pstmt.setDate(5, Date.valueOf(LocalDate.now().minusYears(5 + random.nextInt(15))));
                pstmt.addBatch();

                if (i % 100 == 0) pstmt.executeBatch();
            }
            pstmt.executeBatch();
        }
    }

    private static void insertPatients(Connection conn, int count) throws SQLException {
        String sql = "INSERT INTO patient (pati_name, pati_gender, pati_birth_date, pati_phone) VALUES (?, ?, ?, ?)";
        Random random = new Random();

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (int i = 1; i <= count; i++) {
                pstmt.setString(1, "Patient_" + i);
                pstmt.setString(2, random.nextBoolean() ? "M" : "F");
                pstmt.setDate(3, Date.valueOf(LocalDate.now()
                        .minusYears(20 + random.nextInt(60))
                        .minusMonths(random.nextInt(12))));
                pstmt.setString(4, "1" + String.format("%010d", random.nextInt(1000000000)));
                pstmt.addBatch();

                if (i % 1000 == 0) pstmt.executeBatch();
            }
            pstmt.executeBatch();
        }
    }

    private static void insertMedicines(Connection conn, int count) throws SQLException {
        String sql = "INSERT INTO medicine (med_name, med_price, med_stock) VALUES (?, ?, ?)";
        Random random = new Random();

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (int i = 0; i < count; i++) {
                pstmt.setString(1, MEDICINES[i]);
                pstmt.setDouble(2, 5 + random.nextInt(200)); // price 5-205
                pstmt.setInt(3, 100 + random.nextInt(1900)); // stock 100-2000
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        }
    }

    private static void insertMedicalRecords(Connection conn, int count) throws SQLException {
        String sql = "INSERT INTO medical_record (pati_id, doc_id, mr_diagnosis, mr_visit_date, mr_is_critical, mr_cost) " +
                "VALUES (?, ?, ?, ?, ?, ?)";
        Random random = new Random();

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (int i = 0; i < count; i++) {
                pstmt.setLong(1, random.nextInt(10000) + 1);  // patient_id 1-10000
                pstmt.setInt(2, random.nextInt(50) + 1);     // doctor_id 1-50
                pstmt.setString(3, DIAGNOSES[random.nextInt(DIAGNOSES.length)]);
                pstmt.setDate(4, Date.valueOf(LocalDate.now()
                        .minusMonths(random.nextInt(36))));   // 3年内就诊
                pstmt.setInt(5, random.nextDouble() > 0.9 ? 1 : 0); // 10%危重病例
                pstmt.setDouble(6, 50 + random.nextInt(1950)); // cost 50-2000
                pstmt.addBatch();

                if (i % 1000 == 0) pstmt.executeBatch();
            }
            pstmt.executeBatch();
        }
    }

    private static void insertPrescriptions(Connection conn, int count) throws SQLException {
        String sql = "INSERT INTO prescription (mr_id, med_id, med_quantity, med_dosage) VALUES (?, ?, ?, ?)";
        Random random = new Random();
        HashSet<String> hashSet = new HashSet<>();

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (int i = 0; i < count; i++) {

                String mr_id = (random.nextInt(50000) + 1) + "";
                String med_id = (random.nextInt(10) + 1) + "";

                // 检查主键 (mr_id, med_id) 是否重复 不重复则加入批量处理
                if(hashSet.add(mr_id + "-" + med_id)) {
                    pstmt.setLong(1, Long.parseLong(mr_id));  // mr_id 1-50000
                    pstmt.setInt(2, Integer.parseInt(med_id));     // med_id 1-10
                    pstmt.setInt(3, 1 + random.nextInt(5));      // quantity 1-5
                    pstmt.setString(4, DOSAGES[random.nextInt(DOSAGES.length)]);
                    pstmt.addBatch();
                }
                if (i % 2000 == 0) pstmt.executeBatch();
            }
            pstmt.executeBatch();
        }
    }

    private static void updatePatientNames(Connection conn, int startId, int endId) throws SQLException {
        String sql = "UPDATE patient SET pati_name = ? WHERE pati_id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            Random random = new Random();
            for (int i = startId; i <= endId; i++) { // 假设从 ID 1 开始更新
                String randomName = generateRandomString(random, 4, 8);
                pstmt.setString(1, randomName);
                pstmt.setLong(2, i);
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        }
    }

    private static String generateRandomString(Random random, int minLength, int maxLength) {
        int length = random.nextInt(maxLength - minLength + 1) + minLength;
        StringBuilder sb = new StringBuilder(length);
        for (int i = 0; i < length; i++) {
            char c = (char) (random.nextInt(26) + 'a'); // 生成小写字母
            sb.append(c);
        }
        return sb.toString();
    }
}
